/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBlayer;

import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author bruhn
 */
public class DBProduct implements IFDBProduct {

    private Connection con;
     /** Creates a new instance
     * of DBProduct */
    public DBProduct()
    {
      con = DBConnection.getInstance().getDBcon();
    }

    public ArrayList<Product> getAllProduct(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }

    public Product findProduct(int ID, boolean retriveAssociation)
    {
        String search = "  productID = '" + ID + "'";
        return singleWhere(search, retriveAssociation);
    }

    public Product searchProductname(String name, boolean retriveAssociation) 
    {
        String search = "name like '%" + name + "%'";
        System.out.println("Searchproduct " + search);
        return singleWhere(search, retriveAssociation);
    }

    public int insertProduct(Product prod) {
        
    int rc = -1;
    String query="INSERT INTO persiProduct(name, purchasePrice, salesPrice, rentPrice ,countryOfOrigin ,minStock, maxStock, currentStock, type, supID)  VALUES('"+
	  		prod.getName()  + "','"  +
		    	prod.getPurchasePrice()  + "','"  +
                        prod.getSalesPrice() + "','" +
                        prod.getRentPrice() + "','" +
                        prod.getCountryOfOrigin() + "','" +
                        prod.getMinStock() + "','" +
                        prod.getMaxStock() + "','" +
                        prod.getCurrentStock() + "','" +
                        prod.getType() + "','" +
                        prod.getSupplier().getSupplierID() + "')";

      System.out.println("insert : " + query);
      try{ // insert new product
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in product db: "+ex);
	   }
	   return(rc);
	}

	public int updateProduct(Product prod)
	{
		Product prodObj  = prod;
		int rc=-1;

		String query="UPDATE persiProduct SET "+
                        "name ='" + prodObj.getName() + "', " +
		    	"purchasePrice ='" + prodObj.getPurchasePrice() + "', " +
                        "salesPrice ='" + prodObj.getSalesPrice() + "', " +
                        "rentPrice ='" + prodObj.getRentPrice() + "', " +
                        "countryOfOrigin ='" + prodObj.getCountryOfOrigin() + "', " +
                        "minStock ='" + prodObj.getMinStock() + "', " +
                        "maxStock ='" + prodObj.getMaxStock() + "', " +
                        "currentStock ='" + prodObj.getCurrentStock() + "', " +
                        "type ='" + prodObj.getType() + "' " +
		        " WHERE productID = '"+ prodObj.getProductID() + "'";

                System.out.println("Update query:" + query);
  		try{ // update product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
    }


    public void deleteProduct(int ID)
    {
              int rc = -1;

	  	String query="DELETE FROM persiProduct WHERE productID = '" +
				ID + "'";
                System.out.println(query);
	  	try{ // delete from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex)
                {
	 	 System.out.println("Delete exception in persiProduct db: "+ex);
                }

    }
   private ArrayList<Product> miscWhere(String search, boolean retrieveAssociation)
	{
             ResultSet results;
	    ArrayList<Product> list = new ArrayList<Product>();

	    String query =  buildQuery(search);

            try{ // read the persiProduct from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);

                 while( results.next() ){
	     	 Product prodObj = new Product();
		 prodObj = buildProduct(results);
                 list.add(prodObj);
		}//end while
                 stmt.close();
                 if(retrieveAssociation)
                 {   //The supplier is to be build as well
                     for(Product prodObj : list){
                        IFDBSupplier dbSupplierObj = new DBSupplier();
                        Supplier supplierObj = dbSupplierObj.findSupplier(prodObj.getSupplier().getSupplierID());
                        System.out.println("Supplier is seleceted ");
                        prodObj.setSupplier(supplierObj);
                     }
                 }//end if

		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	//Singelwhere is used when we only select one product
	private Product singleWhere(String search, boolean retrieveAssociation)
	{
		ResultSet results;
		Product prodObj = new Product();

	        String query =  buildQuery(search);
                System.out.println(query);
		try{ // read the persiProduct from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            prodObj = buildProduct(results);
                            //assocaition is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {   //The product is to be build as well

                                IFDBSupplier dbSupplierObj = new DBSupplier();
                                Supplier supplierObj = dbSupplierObj.findSupplier(prodObj.getSupplier().getSupplierID());
                                System.out.println("Supplier is seleceted ");
                                prodObj.setSupplier(supplierObj);
                            }
			}
                        else{ //no product was found
                            prodObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return prodObj;
	}
	//method to build the query
	private String buildQuery(String search)
	{
	    String query="SELECT * FROM persiProduct";

		if (search.length()>0)
			query=query+" WHERE "+ search;

		return query;
	}
	//method to build a product object
	private Product buildProduct(ResultSet results)
      {   Product prodObj = new Product();
          try{
               
                prodObj.setName(results.getString("name"));
                prodObj.setPurchasePrice(results.getDouble("purchasePrice"));
                prodObj.setSalesPrice(results.getDouble("salesPrice"));
                prodObj.setRentPrice(results.getDouble("rentPrice"));
	  	prodObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
	  	prodObj.setMinStock(results.getInt("minStock"));
		prodObj.setMaxStock(results.getInt("maxStock"));
                prodObj.setCurrentStock(results.getInt("currentStock"));
				prodObj.setType((results.getString("type")));
				prodObj.setSupplier(new Supplier(results.getInt("supID")));
          }
         catch(Exception e)
         {
             System.out.println("error in building the product object");
         }
         return prodObj;
      }
}
